package fy.hr.staffRedeploy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.hr.bean.DepBean;
import fy.hr.bean.JobBean;
import fy.hr.bean.OptionBean;
import fy.util.DBUtil;


final class StaffRedeploymentDao {

	private static Log log = LogFactory.getLog(StaffRedeploymentDao.class);

	/**
	 * 所有制造部
	 * 
	 * @return
	 */
	public static StaffRedeployFormBean getStaffRedeploymentBean() {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		StaffRedeployFormBean staffRedeployFormBean = new StaffRedeployFormBean();

		List<OptionBean> beans = new ArrayList<OptionBean>();
		staffRedeployFormBean.setCompanyList(beans);

		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

		/*	pst = conn
					.prepareStatement("select c1.pk_corp, c1.unitname from bd_corp c1 where c1.dr = 0 and not exists ( select * from bd_corp c2 where c1.pk_corp = c2.fathercorp and c1.dr = 0 and c2.dr = 0)");*/
			
			pst = conn
					.prepareStatement("select c1.pk_corp, c1.unitname from bd_corp c1 where c1.dr = 0 AND c1.ISSEAL='N'  AND pk_corp NOT IN ('1001')");

			rs = pst.executeQuery();

			while (rs.next()) {
				OptionBean bean = new OptionBean();

				bean.setId(rs.getString(1));
				bean.setName(rs.getString(2));

				beans.add(bean);
			}

		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("查询ＨＲ中的制造部列表失败");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return staffRedeployFormBean;
	}

	/**
	 * 制造部下所有一级部门
	 * 
	 * @return
	 */
	public static List<OptionBean> querAllDep(String pk_corp) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		List<OptionBean> beans = new ArrayList<OptionBean>();
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());


			pst = conn
					.prepareStatement("select d.pk_deptdoc, d.deptname from bd_deptdoc d left join bd_corp c on d.pk_corp = c.pk_corp where d.dr = 0 and c.pk_corp = ? and d.pk_fathedept is null order by c.pk_corp asc");

			pst.setString(1, pk_corp);

			rs = pst.executeQuery();

			while (rs.next()) {
				OptionBean bean = new OptionBean();

				bean.setId(rs.getString(1));
				bean.setName(rs.getString(2));

				beans.add(bean);
			}

		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("查询ＨＲ制造部下所有一级部门列表失败");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return beans;
	}

	/**
	 * 查询子部门和当前部门的岗位
	 * 
	 * @return
	 */
	public static DepBean querChildrenDepAndPost(String pk_deptdoc) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		DepBean depBean = new DepBean();

		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());


			// 岗位
			pst = conn
					.prepareStatement("select j.jobcode, j.jobname, d.docname from om_job j left join bd_defdoc d on j.jobrank = d.pk_defdoc where j.pk_deptdoc = ? and j.dr = 0 order by j.jobcode");

			pst.setString(1, pk_deptdoc);

			List<JobBean> posts = null;
			rs = pst.executeQuery();
			while (rs.next()) {

				JobBean bean = new JobBean();

				bean.setId(rs.getString(1));
				bean.setName(rs.getString(2));
				bean.setRank(rs.getString(3));

				if (posts == null) {
					posts = new ArrayList<JobBean>();
				}

				posts.add(bean);
			}
			DBUtil.close(rs);
			depBean.setPosts(posts);
			posts = null;

			List<OptionBean> beans = null;
			// 子部门
			pst = conn
					.prepareStatement("select d.pk_deptdoc,d.deptname from bd_deptdoc d where d.pk_fathedept = ? and d.dr  = 0 order by d.deptcode asc");

			pst.setString(1, pk_deptdoc);

			rs = pst.executeQuery();
			while (rs.next()) {

				OptionBean bean = new OptionBean();

				bean.setId(rs.getString(1));
				bean.setName(rs.getString(2));

				if (beans == null) {
					beans = new ArrayList<OptionBean>();
				}
				beans.add(bean);
			}
			depBean.setChildren(beans);
			beans = null;

		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("查询ＨＲ子部门和当前部门的岗位失败");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return depBean;
	}

	public static StaffRedeployFormBean queryMemberInfo(String memberCode) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		StaffRedeployFormBean staffRedeployFormBean = null;

		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			pst = conn
					.prepareStatement("select p.psnname 姓名,c.unitname 单位, j.jobname 岗位, basdoc.basgroupdef4 入厂年月,d1.deptname 部门3,d2.deptname 部门2,d3.deptname 部门1, def1.docname 学历, def2.docname 职级 from bd_psndoc p left join bd_corp c on p.pk_corp = c.pk_corp left join bd_deptdoc d1 on d1.pk_deptdoc = p.pk_deptdoc left join bd_deptdoc d2 on d2.pk_deptdoc = d1.pk_fathedept left join bd_deptdoc d3 on d3.pk_deptdoc = d2.pk_fathedept left join om_job j on p.pk_om_job = j.pk_om_job left join bd_defdoc def1 on def1.pk_defdoc = p.groupdef14 left join bd_defdoc def2 on def2.pk_defdoc = j.jobrank left join bd_psnbasdoc basdoc on p.pk_psnbasdoc=basdoc.pk_psnbasdoc where p.dr = 0 and p.psnclscope = 0 and p.psncode =?");
			// select p.psnname 姓名1,c.unitname 单位2, j.jobname 岗位3,
			// basdoc.basgroupdef4 入厂年月4,d1.deptname 5 部门3,d2.deptname 6
			// 部门2,d3.deptname 7 部门1, def1.docname 8 学历, def2.docname 9 职级 from
			pst.setString(1, memberCode);

			rs = pst.executeQuery();

			if (rs.next()) {
				staffRedeployFormBean = new StaffRedeployFormBean();
				staffRedeployFormBean.setName(rs.getString(1));
				staffRedeployFormBean.setCorp(rs.getString(2));
				staffRedeployFormBean.setNowPost(rs.getString(3));
				staffRedeployFormBean.setJoinTime(rs.getString(4));
				staffRedeployFormBean.setEducation(rs.getString(8));
				staffRedeployFormBean.setNowLevel(rs.getString(9));
				String dep1 = rs.getString(7);
				if (dep1 == null || "".equals(dep1)) {
					dep1 = rs.getString(6);
					if (dep1 == null || "".equals(dep1)) {
						dep1 = rs.getString(5);
						staffRedeployFormBean.setDep1(dep1);
					} else {
						staffRedeployFormBean.setDep1(dep1);
						staffRedeployFormBean.setDep2(rs.getString(5));
					}
				} else {
					staffRedeployFormBean.setDep1(dep1);
					staffRedeployFormBean.setDep2(new StringBuilder()
							.append(rs.getString(6)).append("/")
							.append(rs.getString(5)).toString());
				}
			}

		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("HR系统通过工号查询用户信息异常");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return staffRedeployFormBean;
	}

	// /**
	// * 查询子部门和当前部门的岗位
	// *
	// * @return
	// */
	// public static List<OptionBean> queryManager(String pk_deptdoc) {
	// Connection conn = null;
	// PreparedStatement pst = null;
	// ResultSet rs = null;
	// List<OptionBean> beans = new ArrayList<OptionBean>();
	// try {
	// conn = DBUtil.connect(OA2HRConstant.HRDRIVERCLASSNAME,
	// OA2HRConstant.HRURL, OA2HRConstant.HRUSERNAME,
	// OA2HRConstant.HRPASSWORD);
	//
	// List<String> managerIDList = new ArrayList<String>();
	//
	// // 上级部门 和当前部门主管
	// pst = conn
	// .prepareStatement("select d.pk_fathedept, d.pk_psndoc, d.pk_psndoc2, d.pk_psndoc3 from bd_deptdoc d where d.pk_deptdoc = ?");
	//
	// queryManager(pst, pk_deptdoc, managerIDList);
	//
	// DBUtil.close(pst);
	//
	// if (managerIDList.size() > 0) {
	// pst = conn.prepareStatement("");
	// }
	//
	// } catch (Exception e) {
	// StringBuilder sb = new StringBuilder().append("查询ＨＲ中的制造部列表失败");
	// log.error(sb, e);
	// System.out.println(sb);
	// e.printStackTrace();
	//
	// } finally {
	// DBUtil.close(rs);
	// DBUtil.close(pst);
	// DBUtil.close(conn);
	// }
	// return beans;
	// }
	//
	// /**
	// * 查询部门主管ＩＤ
	// *
	// * @param pst
	// * @param pk_deptdoc
	// * @param managerIDList
	// * @throws Exception
	// */
	// private static void queryManager(PreparedStatement pst, String
	// pk_deptdoc,
	// List<String> managerIDList) throws Exception {
	// ResultSet rs = null;
	// String pk_fathedept = null;
	// try {
	// pst.setString(1, pk_deptdoc);
	//
	// rs = pst.executeQuery();
	//
	// if (rs.next()) {
	// pk_fathedept = rs.getString(1);
	// addManagerID(managerIDList, rs.getString(2));
	// addManagerID(managerIDList, rs.getString(3));
	// addManagerID(managerIDList, rs.getString(4));
	// }
	// } finally {
	// DBUtil.close(rs);
	// }
	// if (pk_fathedept != null && !"".equals(pk_fathedept)) {
	// queryManager(pst, pk_fathedept, managerIDList);
	// }
	// }
	//
	// private static void addManagerID(List<String> managerIDList, String
	// memberID) {
	// if (memberID != null && !"".equals(memberID)
	// && managerIDList.indexOf(memberID) == -1) {
	// managerIDList.add(memberID);
	// }
	// }
}
